----------------------------------------------
-- @name: bitmap_conversion
-- @author: dion cho
-- @description: test for when bitmap transformation wins
----------------------------------------------


-- case1
drop table t1 purge;

create table t1(c1 int, c2 int, c3 int);

insert into t1 
select mod(level,10)+1, 10-mod(level,10)-1, level
from dual
connect by level <= 10000
;

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);
create index t1_n3 on t1(c3);

@gather t1

-- Oracle prefers b*tree to bitmap conversion
explain plan for
select 
  count(c3)
from
  t1
where
  c1 = 1 and
  c2 = 1
;

@plan


-- how about real performance?

select /*+ gather_plan_statistics index(t1 t1(c1)) */
  count(c3)
from
  t1
where
  c1 = 1 and
  c2 = 1
;

spool result1.txt
@stat
spool off


select /*+ gather_plan_statistics 
        index_combine(t1 t1(c1) t1(c2)) */
  count(c3)
from
  t1
where
  c1 = 1 and
  c2 = 1
;

spool result2.txt
@stat
spool off

@diff result1.txt result2.txt

-- how about index join?
select /*+ gather_plan_statistics 
        index_join(t1 t1(c1) t1(c2)) */
  count(c3)
from
  t1
where
  c1 = 1 and
  c2 = 1
;

spool result3.txt
@stat
spool off

@diff result2.txt result3.txt


------------------------------------------------------
-- case2
drop table t1 purge;

create table t1(c1 int, c2 int, c3 int);

insert into t1 
select mod(level,10)+1, mod(level,10)+1, level
from dual
connect by level <= 10000
;

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);
create index t1_n3 on t1(c3);

@gather t1


-- Oracle prefers b*tree to bitmap conversion
explain plan for
select 
  count(c3)
from
  t1
where
  c1 = 1 and
  c2 = 1
;

@plan


-- how about real performance?

select /*+ gather_plan_statistics index(t1 t1(c1)) */
  count(c3)
from
  t1
where
  c1 = 1 and
  c2 = 1
;

spool result1.txt
@stat
spool off



select /*+ gather_plan_statistics
          index_combine(t1 t1(c1) t1(c2)) */
  count(c3)
from
  t1
where
  c1 = 1 and
  c2 = 1
;


spool result2.txt
@stat
spool off

@diff result1.txt result2.txt

-- how about index join?
select /*+ gather_plan_statistics 
        index_join(t1 t1(c1) t1(c2)) */
  count(c3)
from
  t1
where
  c1 = 1 and
  c2 = 1
;

spool result3.txt
@stat
spool off

@diff result2.txt result3.txt
